﻿using Dapper;
using OddUserManager.Data.IRepositories;
using OddUserManager.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace OddUserManager.Data.Repositories
{
    public class OddUserRepository : DapperBaseRepository<OddUser, int>, IOddUserRepository
    {
        public List<OddUser> GetAllOddUsers()
        {
            var sql = $"select * from OddUser order by SiteSort asc,SortIndex asc ";
            using (var con = GetOpenConnection())
            {
                return con.Query<OddUser>(sql, null, null, true, 3000, null).AsList();
            }
        }

        public new OddUser Get(int id)
        {
            OddUser t = null;
            using (var con = GetOpenConnection())
            {
                var sql = $"select * from OddUser where Id={id.ToString()};select * from Site where Id=(select SiteId from OddUser where Id={id.ToString()})";
                t = con.QueryMultiple(sql, null, null).Map<OddUser, Site, int>
                      (
                      m => m.SiteId,
                      c => c.Id,
                      (m, cs) => { m.Site = cs.ToList().FirstOrDefault(); }
                      ).FirstOrDefault();
            }
            return t;
        }

        public List<OddUser> GetAllUsersWithOdds(int num)
        {
            List<OddUser> list = new List<OddUser>();
            //using (var con = GetOpenConnection())
            //{
            //    var sql = $"select * from OddUser;select a.* from Odd a where {num} > (select count(*) from odd where CreateOddUserId = a.CreateOddUserId and CreateTime > a.CreateTime) order by a.CreateOddUserId,a.CreateTime ";
            //    list = con.QueryMultiple(sql, null, null).Map<OddUser, Odd, int>
            //          (
            //          m => m.Id,
            //          c => c.CreateOddUserId,
            //          (m, cs) => { m.OddList = cs.ToList(); }
            //          ).ToList();
            //    if (null != list)
            //    {
            //        var sites = con.Query<Site>("select * from Site").ToList();
            //        list.ForEach(p =>
            //        {
            //            p.Site = sites.FirstOrDefault(x => x.Id == p.SiteId);
            //        });
            //    }
            //}
            using (var con = GetOpenConnection())
            {
                var sql = $"select o.*,s.*, od.* from OddUser o left join [Site] s on o.SiteId=s.Id left join Odd od on o.Id = od.CreateOddUserId where {num}> (select count(*) from odd where CreateOddUserId = od.CreateOddUserId and CreateTime > od.CreateTime) order by od.CreateOddUserId,od.CreateTime";
                var clist= con.Query<OddUser, Site, Odd,OddUser>(sql, (o, s, od) =>
                {
                    o.Site = s;
                    var currentClient = list.Find(x => x.Id == o.Id);
                    if (currentClient == null)
                    {
                        if (od != null)
                        {
                            o.OddList.Add(od);
                        }
                        list.Add(o);
                        return o;
                    }
                    else
                    {
                        currentClient.OddList.Add(od);
                        return currentClient;
                    }

                },null,null,true,"Id",3000,null).ToList();

            }

            return list;
        }


        


        public List<OddUser> GetOddUserByParam(OddUser param)
        {
            var sql = $"select A.*,B.* from (select t.*,(select UserName from AppUser where Id = t.CreateAppUserId) as CreateAppUserName,(select UserName from AppUser where Id = t.ModAppUserId) as ModAppUserName from OddUser t where 1=1 ";
            if (param != null) {
                if (!IsStringNull(param.Account)) {
                    sql += $" and Account = '{SwapSqlStr(param.Account)}' ";
                }
                if (!IsStringNull(param.NickName)) {
                    sql += $" and NickName = '{SwapSqlStr(param.NickName)}'";
                }
                if (param.SiteId > 0) {
                    sql += $" and SiteId = {param.SiteId} ";
                }
                if (!IsStringNull(param.SiteIds))
                {
                    sql += $" and SiteId in ({SwapSqlStr(param.SiteIds)})";
                }
            }
            sql += " ) A left join  Site B on A.SiteId = B.Id  ";

            var lookup = new Dictionary<string, OddUser>();
            using (var con = GetOpenConnection())
            {
                return con.Query<OddUser, Site, OddUser>(sql, (o, s) => {
                    OddUser tmp;
                    if (!lookup.TryGetValue(o.Id + "", out tmp))
                    {
                        tmp = o;
                        lookup.Add(o.Id + "", tmp);
                    }

                    tmp.Site = s;

                    return o;
                }).AsList();
            }
        }

        public List<OddUser> GetOddUsersBySiteId(int siteId)
        {
            var sql = $"select * from OddUser where SiteId = {siteId} order by SortIndex desc ";
            using (var con = GetOpenConnection())
            {
                return con.Query<OddUser>(sql, null, null, true, 3000, null).AsList();
            }
        }

        public bool InsertOrUpdate(OddUser entity)
        {
            var t = Get(entity.Id);
            if (null == t)
            {
                return Insert(entity);
            }
            else
            {
                return Update(entity);
            }
        }

        public IEnumerable<OddUser> LoadPageList(int startPage, int pageSize, out int rowCount, Func<OddUser, bool> where, Func<OddUser, object> order)
        {
            var result = GetAllList();
            if (where != null)
                result = result.Where(where);
            if (order != null)
                result = result.OrderBy(order);
            else
                result = result.OrderBy(m => m.Id);
            rowCount = result.Count();
            return result.Skip((startPage - 1) * pageSize).Take(pageSize);
        }

        public PageViewCollection<OddUser> SearchOddUserPages(OddUser param, int pageIndex, int pageSize, string sortColumn, string sortDirection)
        {
            PageViewCollection<OddUser> collection = new PageViewCollection<OddUser>();
            if (IsStringNull(sortColumn))
            {
                sortColumn = "Id";
            }
            if (IsStringNull(sortDirection))
            {
                sortDirection = "asc";
            }
            using (var con = GetOpenConnection())
            {
                var countHeadSql = " select count(id) from OddUser where 1=1 ";
                var pageHeadSql = $"select M.*,C.* from ( select top {pageSize} * from (select ROW_NUMBER() OVER ( ORDER BY SiteSort asc, {sortColumn} {sortDirection} ) AS rownumber,(select UserName from AppUser where Id = t.CreateAppUserId) as CreateAppUserName,(select UserName from AppUser where Id = t.ModAppUserId) as ModAppUserName, t.* from OddUser t where 1=1 ";
                var conditionSql = " ";
                if (param != null)
                {
                    if (!IsStringNull(param.Account))
                    {

                        conditionSql += $" and Account like '%{SwapSqlStr(param.Account)}%' ";
                    }

                    if (!IsStringNull(param.SiteIds))
                    {
                        conditionSql += $" and SiteId in ({SwapSqlStr(param.SiteIds)})";
                    }

                }

                // 获取记录总数
                collection.TotalCount = con.Query<int>($" {countHeadSql} {conditionSql} ").FirstOrDefault();
                collection.PageIndex = pageIndex;
                collection.PageSize = pageSize;
                collection.PageCount = Math.Ceiling(Convert.ToDecimal(collection.TotalCount) / pageSize);

                var pageFootSql = $" ) A WHERE rownumber > {(pageIndex - 1) * pageSize} ) M left join Site C on M.SiteId = C.Id ";
                var lookup = new Dictionary<string, OddUser>();
                var _sql = $" {pageHeadSql} {conditionSql} {pageFootSql}";

                collection.Result = con.Query<OddUser,Site,OddUser>(_sql,(o,s)=> {
                    OddUser tmp;
                    if (!lookup.TryGetValue(o.Id+"", out tmp))
                    {
                        tmp = o;
                        lookup.Add(o.Id +"", tmp);
                    }

                    tmp.Site = s;

                    return o; });

            }

            return collection;
        }

        public void UpdateSiteSort()
        {
            var sql = $"update OddUser  set SiteSort = (select t.SortValue from Site t where t.Id = SiteId ) ";
            using (var con = GetOpenConnection())
            {
                con.Execute(sql, null, null, 3000, null);
            }
        }

        private bool IsStringNull(Object obj)
        {
            if (obj == null) return true;
            if (obj.ToString().Trim().Length < 1) return true;
            return false;
        }

        private string SwapSqlStr(string str)
        {
            if (IsStringNull(str)) return str;

            str = str.Replace(";", "");
            str = str.Replace("'", "");
            str = str.Replace("/", "");
            str = str.Replace("&", "&amp;");
            str = str.Replace("<", "&lt;");
            str = str.Replace(">", "&gt;");
            str = str.Replace("--", "");
            str = str.Replace("%", "");

            return str;
        }

    }
}
